import os,datetime,json
import xdrlib,xlrd,xlwt,xlutils

class HandleExcel(object):
    def __init__(self):
        self.base_path =os.path.dirname(os.path.dirname(os.path.abspath(__file__)))

    def get_excel_data(self ,row_number ,page_number ,excel_name='test.xlsx'):
        '''
            :param row_number:  每页显示的行数
            :param page_number:  页码
            :param excel_name:  读取的Excel 表格
            :return: {"title":[],"content":[{},{}]}
        '''
        excel_path = os.path.join(self.base_path, 'db/{}'.format(excel_name))
        excel_path = excel_path.replace('\\', '/')
        # print(self.base_path)
        # print(excel_path)
        excel_data = {}  # 获取所有内容的字典
        try:
            # 将路径, 显示行数, 页码传过去
            sheet_rows,title,content = self.handle_page(excel_path,row_number,page_number)

            excel_data['sheet_rows'] = sheet_rows  # 总行数 放到字典中 {n,[],[{},{}]}
            excel_data['title'] = title  # title 放到字典中 {n,[],[{},{}]}
            excel_data['content'] = content  # 内容 放到字典中 {n,[],[{},{}]}
        except Exception as ex:
            print("ex [{}]".format(ex))
        return excel_data  # {n,[],[{},{}]} 返回一个字典

    def  handle_page(self,excel_path,row_number,page_number):
        '''
        :param excel_path: Excel的路径
        :param row_number: 显示的行数
        :param page_number:  当前显示的页码
        :return: title,content
        '''
        r_book = xlrd.open_workbook(excel_path)  # 打开excel 文件对象
        # r_sheet=r_book.sheet_by_name('Sheet1') # 通过表格名字获取
        r_sheet = r_book.sheet_by_index(0)  # 通过索引获取 表格
        sheet_rows = r_sheet.nrows  # 总行数
        sheet_cols = r_sheet.ncols  # 总列数
        sheet_row_title = r_sheet.row_values(0)  # 第一行数据 title
        sheet_col_id = r_sheet.col_values(0)  # 第一列数据

        row_number = int(row_number)
        page_number = int(page_number)
        if row_number < sheet_rows and page_number * row_number < sheet_rows:  # 显示行数和Excel 中的总行数比较
            start_row = (page_number-1) * row_number  # 根据页码和显示行数得到该页的起始行
            end_row = start_row+row_number+1  # 根据页码和显示行数得到该页的结束行 # 取出起始行到结束行(因为第一行是title所以+1)
        elif sheet_rows < page_number * row_number:  # 最后几行的情况
            start_row = (page_number - 1) * row_number  # 根据页码和显示行数得到该页的起始行
            end_row = sheet_rows
        elif sheet_rows < row_number:  # 总行数小于显示行数的情况
            start_row = 1  # 根据页码和显示行数得到该页的起始行
            end_row = sheet_rows  # 根据页码和显示行数得到该页的结束行

        print(end_row)
        content = []  # 存放内容的列表
        for row in range(start_row,end_row):  # 取出起始行到结束行(因为第一行是title所以+1)
            if row == 0:  # 第一行是标题，不要
                continue
            else:
                apps = {}
                for col in range(sheet_cols):  # 每列
                    if r_sheet.cell(row, col).ctype == 3:  # 处理日期格式的值
                        date_value = xlrd.xldate_as_tuple(r_sheet.cell(row, col).value, datemode=0)
                        date_value = datetime.date(*date_value[:3]).strftime('%Y/%m/%d')
                        apps[sheet_row_title[col]] = date_value
                    else:
                        # 以标题为key 组成字典
                        apps[sheet_row_title[col]] = r_sheet.cell(row, col).value
            content.append(apps)

        return sheet_rows, sheet_row_title, content






